import pymysql
import pandas as pd
import logging

from calc.StockCalculate import StockCalculate

mysql_host = "127.0.0.1"
mysql_port = 3306

logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s | %(filename)s - {%(lineno)d} - [%(levelname)s] - %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S', encoding='utf-8')

# create mysql connect
connect = pymysql.connect(host=mysql_host, port=mysql_port, user='root', passwd='hxsoft.net', database='stock')
cursor = connect.cursor()

cursor.execute("select stock_code  from stock_code")
row_list = cursor.fetchall()
for row in row_list:
    stock_code = row[0]

    cursor.execute(
        "select stock_code code,stock_date date,open,close,high,low,vol,pchange from stock_details where stock_code='" + stock_code + "' order by stock_date asc")
    data_list = cursor.fetchall()
    calculate = StockCalculate(data_list)
    calculate.calculate()
    df = calculate.update()

    if df is not None:
        for i in range(df.shape[0]):
            stock_code = df.loc[i, "code"]
            stock_date = df.loc[i, "date"].strftime("%Y-%m-%d")
            zl1 = df.loc[i, "zl1"]
            zl2 = df.loc[i, "zl2"]
            ma5 = df.loc[i, "ma5"]
            ma10 = df.loc[i, "ma10"]
            ma20 = df.loc[i, "ma20"]
            ma30 = df.loc[i, "ma30"]
            ma60 = df.loc[i, "ma60"]
            vol5 = df.loc[i, "vol5"]
            vol10 = df.loc[i, "vol10"]
            vol20 = df.loc[i, "vol20"]
            dif = df.loc[i, "dif"]
            dea = df.loc[i, "dea"]
            macd = df.loc[i, "macd"]
            kdj_k = df.loc[i, "kdj_k"]
            kdj_d = df.loc[i, "kdj_d"]
            kdj_j = df.loc[i, "kdj_j"]
            rsi_6 = df.loc[i, "rsi_6"]
            atr_14 = df.loc[i, "atr_14"]
            c_d1 = df.loc[i, "c_d1"]
            c_d3 = df.loc[i, "c_d3"]
            c_d5 = df.loc[i, "c_d5"]
            c_d10 = df.loc[i, "c_d10"]

            h_d1 = df.loc[i, "h_d1"]
            h_d3 = df.loc[i, "h_d3"]
            h_d5 = df.loc[i, "h_d5"]
            h_d10 = df.loc[i, "h_d10"]

            l_d1 = df.loc[i, "l_d1"]
            l_d3 = df.loc[i, "l_d3"]
            l_d5 = df.loc[i, "l_d5"]
            l_d10 = df.loc[i, "l_d10"]

            cursor.execute(
                "select count(1) from stock_value where stock_code = '" + stock_code + "' and stock_date = '" + stock_date + "'")
            result = cursor.fetchone()
            if result is None or result[0] == 0:
                # save
                sql = "insert into stock_value(stock_code,stock_date,zl1,zl2,ma5,ma10,ma20,ma30,ma60,v5,v10,v20,dif,dea,macd,kdj_k,kdj_d,kdj_j,rsi_6,atr_14,c_d1,c_d3,c_d5,c_d10,h_d1,h_d3,h_d5,h_d10,l_d1,l_d3,l_d5,l_d10) values ('{0}','{1}',{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19},{20},{21},{22},{23},{24},{25},{26},{27},{28},{29},{30},{31})".format(
                    stock_code, stock_date, 'null' if pd.isnull(zl1) else zl1, 'null' if pd.isnull(zl2) else zl2,
                    'null' if pd.isnull(ma5) else ma5, 'null' if pd.isnull(ma10) else ma10,
                    'null' if pd.isnull(ma20) else ma20,
                    'null' if pd.isnull(ma30) else ma30, 'null' if pd.isnull(ma60) else ma60,
                    'null' if pd.isnull(vol5) else vol5,
                    'null' if pd.isnull(vol10) else vol10, 'null' if pd.isnull(vol20) else vol20,
                    'null' if pd.isnull(dif) else dif,
                    'null' if pd.isnull(dea) else dea, 'null' if pd.isnull(macd) else macd,
                    'null' if pd.isnull(kdj_k) else kdj_k, 'null' if pd.isnull(kdj_d) else kdj_d,
                    'null' if pd.isnull(kdj_j) else kdj_j, 'null' if pd.isnull(rsi_6) else rsi_6,
                    'null' if pd.isnull(atr_14) else atr_14, 'null' if pd.isnull(c_d1) else c_d1,
                    'null' if pd.isnull(c_d3) else c_d3, 'null' if pd.isnull(c_d5) else c_d5,
                    'null' if pd.isnull(c_d10) else c_d10,
                    'null' if pd.isnull(h_d1) else h_d1, 'null' if pd.isnull(h_d3) else h_d3,
                    'null' if pd.isnull(h_d5) else h_d5,
                    'null' if pd.isnull(h_d10) else h_d10, 'null' if pd.isnull(l_d1) else l_d1,
                    'null' if pd.isnull(l_d3) else l_d3,
                    'null' if pd.isnull(l_d5) else l_d5, 'null' if pd.isnull(l_d10) else l_d10)
                cursor.execute(sql)

        connect.commit()
    logging.info("stock_code: {0} finished".format(stock_code))

cursor.close()
connect.close()
